﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using MongoDB.Bson;
using lo = sqCore.sqLogger;

namespace sqCore
{
    /* Docs:
     * Summary
        * SQL query Job object for SqlA Tasks.
     * Override Variables
        * IsDone - If false, this job not runed.
        * ExecuteTime - Time that the job executed.
        * Result - The results of the execute.
     * Variables
        * DB - SqlA db object.
        * Query - The query.
        * QueryResults - The result of the query execute.
     */
    public class sqSqlQueryJob : sqJob
    {
        public sqDB DB { get; set; }        
        public string Query { get; set; }
        public sqQueryTableResult QueryResult { get; set; } //TODO: generic


        /* Docs:
         * Summary: Initialize the job.
         */
        public sqSqlQueryJob(sqDB DB, string Query = "select * from dual", DateTime? QueryTime = null) : base()
        {
            if (QueryTime == null)
                this.ExecuteTime = DateTime.Now;
            this.DB = DB;
            this.QueryResult = null;
            this.Query = Query;
            this.IsDone = false;
        }

        /* Docs:
        *Summary: Execute job and set results.
        */
        public override void executeJob()
        {
            OleDbConnection conn=this.DB.connect(); //Create connection to the DB
            OleDbCommand sqlCMD = new OleDbCommand(this.Query, conn);
            OleDbDataReader dr = null;

            if (!this.IsDone)
                try
                {
                    conn.Open();                   
                    dr = sqlCMD.ExecuteReader(); //Execute the query
                    this.QueryResult = new sqQueryTableResult(new List<object>(), new List<List<object>>()); //Set the results.

                    for (int i = 0;  i < dr.FieldCount; ++i)
                        this.QueryResult.Titles.Add(dr.GetName(i));
          
                    while (dr.Read())
                    {
                        Object[] row = new Object[dr.FieldCount];
                        List<object> Row = new List<object>();
                        dr.GetValues(row);
                        foreach (object tab in row)
                            Row.Add(tab);
                        this.QueryResult.Params.Add(Row);
                    }
                    dr.Close(); //cleaning up.
                    lo.writeLog(LogMessage: "Query job is succssesflly done.", Param: this.Query, debugOnly: true);
                }
                catch (System.Exception ex)
                {
                    lo.writeLog(LogMessage: "Failed to execute the job.", Params: new List<string> {ex.ToString(),this.ToJson()}, isError: true);
                }
                finally
                {
                    dr.Close();
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    this.ExecuteTime = DateTime.Now;
                    this.IsDone = true;                    
                }
        }

        /* Docs:
        *Summary: Return results.
        */
        public override object getResult()
        {
            return this.QueryResult;
        }

        /* Docs:
        *Summary: Return new instance that similar to this job befor execute.
        */
        public override sqJob renew()
        {
            return new sqSqlQueryJob(this.DB, this.Query);
        }

        /* Docs:
        *Summary: Return new instance that equles to this job before/after execute.
        */
        public override sqJob copy()
        {
            sqSqlQueryJob jobC = new sqSqlQueryJob(this.DB, this.Query,this.ExecuteTime);
            jobC.IsDone = this.IsDone;
            jobC.QueryResult = this.QueryResult;
            return jobC;
        }
    }
}
